package com.kr.weitao.util.jdbc;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.kr.weitao.util.StringUtils;
import com.kr.weitao.util.TimeUtils;
import lombok.extern.log4j.Log4j2;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by xdl on 2017/5/25. JdbcClient 针对数据库 增删改查
 */
@Log4j2
public class JdbcClient {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void setJdbcTemplate(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    /**
     * 新增
     *
     * @param sql
     * @param params
     * @return 记录ID
     */
    public long insert(String sql, Object[] params) {

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(
                new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                        PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                        if (params == null) {
                            return ps;
                        }
                        Object op = null;
                        int alength = params.length;
                        for (int i = 0; i < alength; ++i) {
                            op = params[i];
//              StatementCreatorUtils.setParameterValue(ps, i + 1, -2147483648, op);
                            StatementCreatorUtils.setParameterValue(ps, i + 1, -2147483648, op == null ? op : StringUtils.valueOf(op));
                        }
                        return ps;
                    }
                },
                keyHolder);
        return keyHolder.getKey().intValue();
    }

    /**
     * 更新
     *
     * @param sql
     * @param params
     * @return 影响的行数
     */
    public int update(String sql, Object[] params) {
        log.debug("update sql:" + sql);
        List<Object> list = null;
        if (params != null && params.length > 0) {
            list = new ArrayList<>();
            for (Object o : params) {
                log.debug("value2:" + o);
                list.add(o == null ? o : StringUtils.valueOf(o));
            }
        }
        return jdbcTemplate.update(sql, list != null && list.size() > 0 ? list.toArray() : null);
//    return jdbcTemplate.update(sql, params);
    }

    /**
     * 删除
     *
     * @param sql
     * @param params
     * @return 受影响的行数
     */
    public int delete(String sql, Object[] params) {
        return jdbcTemplate.update(sql, params);
    }

    /**
     * 说明：查询，返回的是Json对象
     *
     * @return JSONObject
     */
    public JSONObject getJSONObject(String sql, Object[] params) {
        return getJSONObject(sql, params, false);
    }

    public JSONObject getJSONObject(String sql, Object[] params, final boolean toUpper) {
        log.debug("save sql:" + sql);
        if (params != null && params.length > 0) {
            for (Object o : params) {
                log.debug("value:" + o);
            }
        }
        return jdbcTemplate.query(
                sql,
                params,
                new ResultSetExtractor<JSONObject>() {
                    public JSONObject extractData(ResultSet resultSet)
                            throws SQLException, DataAccessException {
                        ResultSetMetaData rsd = resultSet.getMetaData();
                        int clength = rsd.getColumnCount();
                        String columnName;
                        try {
                            if (resultSet.next()) {
                                JSONObject jo = new JSONObject();

                                for (int i = 0; i < clength; i++) {
                                    columnName = rsd.getColumnLabel(i + 1);
                                    columnName = toUpper ? columnName.toUpperCase() : columnName.toLowerCase();
                                    jo.put(columnName, resultSet.getObject(i + 1));
                                }
                                return jo;
                            }
                        } catch (Exception e) {
                            log.debug("get data error:" + e.getLocalizedMessage());
                            e.printStackTrace();
                        }
                        return null;
                    }
                });
    }

    /**
     * 查询
     *
     * @return JSONArray json数组
     */
    public JSONArray getJSONArray(String sql, Object[] params) {
        JSONArray ja = getJSONArray(sql, params, false);
        return ja;
    }

    public JSONArray getJSONArray(String sql, Object[] params, final boolean toUpper) {
        log.debug("get sql:" + sql);
        if (params != null && params.length > 0) {
            for (Object o : params) {
                log.debug("value:" + o);
            }
        }

        return jdbcTemplate.query(
                sql,
                params,
                new ResultSetExtractor<JSONArray>() {
                    public JSONArray extractData(ResultSet resultSet)
                            throws SQLException, DataAccessException {
                        ResultSetMetaData rsd = resultSet.getMetaData();
                        int clength = rsd.getColumnCount();
                        JSONArray ja = new JSONArray();
                        String columnName;
                        try {
                            while (resultSet.next()) {
                                JSONObject jo = new JSONObject();
                                for (int i = 0; i < clength; i++) {
                                    columnName = rsd.getColumnLabel(i + 1);
                                    columnName = toUpper ? columnName.toUpperCase() : columnName.toLowerCase();
                                    //region 处理时间
                                    if ("createdate".equalsIgnoreCase(columnName) || "modifydate".equalsIgnoreCase(columnName)) {
                                        jo.put(columnName,
                                                TimeUtils.DATETIME_FORMAT_DATE.format(TimeUtils.DATETIME_FORMAT_DATE.parse(resultSet.getObject(i + 1).toString())));
                                    } else {
                                        //endregion
                                        jo.put(columnName, resultSet.getObject(i + 1));
                                    }
                                }
                                ja.add(jo);
                            }
                        } catch (Exception e) {
                            log.debug("get data error:" + e.getLocalizedMessage());
                            e.printStackTrace();
                        }
                        log.debug("---->SpringJdbcService:" + ja.toString());
                        return ja;
                    }
                });
    }

    /**
     * 说明：查询记录，例如：查询手机号码是否存在
     *
     * @param sql
     * @param params
     * @return
     */
    public int getCount(String sql, Object[] params) {
        return doQueryOne(sql, params, Integer.class);
    }

    /**
     * 说明：查询一条记录，
     *
     * @param sql
     * @param params
     * @return
     */
    public <T> T doQueryOne(String sql, Object[] params, Class<T> returnTtype) {
        log.debug("save sql:" + sql);
        if (params != null && params.length > 0) {
            for (Object o : params) {
                log.debug("value:" + o);
            }
        }
        T o = null;
        try {
            o = jdbcTemplate.queryForObject(sql, params, returnTtype);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }
}
